The present invention relates to electronic data processing, and more particularly concerns database mechanisms for retrieving combined or aggregated data.
Many databases of interest to businesses and other users contain data that is multidimensional and that has multiple levels in some or all of the dimensions. For example, a business might wish to organize sales data by geographic location, by management structure, and by measures. The locations dimension might have levels representing global regions, countries, provinces/states, and cities. The management dimension might have levels denoting upper management, lower management, and field offices. Measures might include sales volumes and amounts.
Users rarely wish to see all the data in a database. Instead, they query the database to extract certain portions of it into a report, table, or other result. A report from a multidimensional database often contains data taken from multiple levels along one or more of its axes or dimensions. Although report entries or cells from the lowest levels might correspond to data taken directly from the stored database, other report cells might contain values representing combinations of the individual data at lower database levels. A typical report might ask for sales volumes for certain cities in selected provinces and countries, along with totals for the provinces and countries in which the cities are located. Cells containing combined data from lower-level database data are known as aggregate cells. Database systems have long provided facilities for aggregating the data values of a number of specified database items into a single cell of a report. The Structured Query Language (SQL) database language, for instance, permits a query to specify the value of a table entry or cell as a sum, average, count, etc. of specified data items in a database.
More recently, database systems such as the Microsoft(copyright) SQL Server from Microsoft Corp. have included facilities for including aggregate cells in reports from multidimensional, multilevel databases. The Online Analytic Processing (OLAP) Services feature of this server permits a user to specify that a certain report cell is to contain, e.g., the sales volume of an entire country, while other cells in the same report contain sales volumes for individual provinces and/or cities of that country.
Such OLAP aggregate cells, however, always report the combination of all data items from lower levels, even when less than all of the lower-level items appear in the report. In the preceding example, the aggregate cell always contains the sales volume for all provinces and cities of the selected country, even when only a few of the individual provinces and/or cities are selected for inclusion in the report.
Sometimes, however, a user wishes to see data values for a higher level that represent the aggregate of only those cells that the query specifies for the lower levels. That is, for truncated hierarchies, data should aggregate only to the extent that their descendants or other components are included in the report generated from a query specification. Conventional database systems are inflexible in this regard. A query can only select all or nothing.
A need therefore exists for a database system facility for permitting a query to select the range of data for higher levels of a database from less than all of the data at the lower levels.
The present invention offers a facility for multiple-level database systems that permits a query or other input to specify a xe2x80x9cwhat you see is what you getxe2x80x9d kind of aggregate entry or cell in a report, where the aggregate value represents only the lower-level data or components that are also specified in the query. Such a facility furthers the basic concept of a database in selecting only certain data from a database, by extending that concept to higher levels of a multi-level database as well.
This facility, called xe2x80x98visual totalsxe2x80x99, receives a query or other input specifying a visual total for certain data, assembles certain data from the database into base cells representing direct data from the database, and aggregates data only from the specified contributors into an aggregate cell representing higher-level data in a report. A visual total is a partial aggregate of the lower-level descendants of a higher-level node at any level of a database. A report form a query including a visual-totals specification includes data for a database node at a higher level that is not aggregated from all of the descendents of that node, but rather only from those xe2x80x98contributorxe2x80x99 nodes at a lower level that are explicitly designated in the query. Stated another way, for truncated hierarchies, data should aggregate only to the extent that their descendants or other components are included in the report generated from a query specification. An aggregation can be any combination or function of data items at lower levels of the database hierarchy that includes a higher-level node specified in a query.